package org.jeecg.modules.online.config.util;

import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.toolkit.JdbcUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtil;
import org.jeecg.common.constant.DataBaseConstant;
import org.jeecg.common.util.CommonUtils;
import org.jeecg.common.util.SpringContextUtils;
import org.jeecg.common.util.dynamic.db.DbTypeUtils;
import org.jeecg.common.util.oConvertUtils;
import org.jeecg.modules.online.config.exception.DBException;
import org.jeecg.modules.online.config.model.DataBaseConfig;
import org.jeecg.modules.online.config.service.DbTableHandleI;
import org.jeecg.modules.online.config.service.impl.*;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库工具类
 * @Author scott
 * 2013年7月6日
 */
@Slf4j
public class DbTableUtil {
	public static String DB_TYPE = "";

	/**
	 * 根据数据库类型构造不同handler
	 * @return
	 * @throws SQLException
	 * @throws DBException
	 */
	public static DbTableHandleI getTableHandle() throws SQLException,DBException {
		return getTableHandle(null);
	}
	
	/**
	  * 根据数据库类型构造不同handler
	 * @return
	 * @throws SQLException
	 * @throws DBException
	 */
	public static DbTableHandleI getTableHandle(DataBaseConfig dataBaseConfig) throws SQLException,DBException {
		DbTableHandleI dbTableHandle = null;
		//update-begin-author:taoyan date:20210831 for: 【数据库兼容性】online表单同步逻辑 LOWCOD-1766
		DbType dbTypeEnum = DbTableUtil.getDbType(dataBaseConfig);
		String dbType = DbTypeUtils.getDbTypeString(dbTypeEnum);
		//update-end-author:taoyan date:20210831 for: 【数据库兼容性】online表单同步逻辑 LOWCOD-1766
		//update-begin-author:taoyan date:20211217 for:JTC-68【online表单】达梦数据库数据库同步，时间类型不对
		// TODO -author:taoyan date:20211217 for:获取dbType的时候 dm被包含在oracle里了导致这里获取不到dm真实的实现类
		if(DbType.DM.equals(dbTypeEnum)){
			return new DbTableDmHandleImpl();
		}
		//update-end-author:taoyan date:20211217 for:JTC-68【online表单】达梦数据库数据库同步，时间类型不对
		switch (dbType) {
		case DataBaseConstant.DB_TYPE_MYSQL:
			dbTableHandle = new DbTableMysqlHandleImpl();
			break;
		case DataBaseConstant.DB_TYPE_MARIADB:
			dbTableHandle = new DbTableMysqlHandleImpl();
			break;
		case DataBaseConstant.DB_TYPE_ORACLE:
			dbTableHandle = new DbTableOracleHandleImpl();
			break;
		case DataBaseConstant.DB_TYPE_DM:
			dbTableHandle = new DbTableDmHandleImpl();
			break;
		case DataBaseConstant.DB_TYPE_SQLSERVER:
			dbTableHandle = new DbTableSQLServerHandleImpl();
			break;
		case DataBaseConstant.DB_TYPE_POSTGRESQL:
			dbTableHandle = new DbTablePostgresHandleImpl();
			break;
		case DataBaseConstant.DB_TYPE_DB2:
			dbTableHandle = new DbTableDB2HandleImpl();
			break;
		case DataBaseConstant.DB_TYPE_HSQL:
			dbTableHandle = new DbTableHyperSQLHandleImpl();
			break;
		default:
			//TODO 临时先用 DbTableMysqlHandleImpl
			dbTableHandle = new DbTableMysqlHandleImpl();
			break;
		}
		return dbTableHandle;
	}
	
	/**
	  * 获取数据库连接对象
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		DataSource dataSource = SpringContextUtils.getApplicationContext().getBean(DataSource.class);
		return dataSource.getConnection();
	}
	
	/**
 	  * 获取数据库类型
	 * @return
	 * @throws SQLException
	 * @throws DBException
	 */
	public static String getDatabaseType() throws SQLException, DBException {
		if(StringUtil.isNotEmpty(DbTableUtil.DB_TYPE)){
			return DbTableUtil.DB_TYPE;
		}
		DataSource dataSource = SpringContextUtils.getApplicationContext().getBean(DataSource.class);
		return getDatabaseType(dataSource);
	}

	/**
	 * 判断是不是oracle数据库 是的话存在大小写问题
	 * @return
	 */
	public static boolean hasLetterCaseProblem(){
		try {
			return DataBaseConstant.DB_TYPE_ORACLE.equals(getDatabaseType());
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (DBException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	/**
	 * 获取数据库类型
	 * @param dataSource
	 * @return
	 * @throws SQLException
	 * @throws DBException
	 */
	public static String getDatabaseType(DataSource dataSource) throws SQLException, DBException {
		if("".equals(DB_TYPE)) {
			Connection connection = dataSource.getConnection();
			try {
				DatabaseMetaData md = connection.getMetaData();
				String dbType = md.getDatabaseProductName().toLowerCase();
				if(dbType.indexOf("mysql")>=0) {
					DB_TYPE = DataBaseConstant.DB_TYPE_MYSQL;
				}else if(dbType.indexOf("oracle")>=0) {
					DB_TYPE = DataBaseConstant.DB_TYPE_ORACLE;
				}else if(dbType.indexOf("dm")>=0) {
					DB_TYPE = DataBaseConstant.DB_TYPE_DM;
				}else if(dbType.indexOf("sqlserver")>=0||dbType.indexOf("sql server")>=0) {
					DB_TYPE = DataBaseConstant.DB_TYPE_SQLSERVER;
				}else if(dbType.indexOf("postgresql")>=0) {
					DB_TYPE = DataBaseConstant.DB_TYPE_POSTGRESQL;
				}else if(dbType.indexOf("mariadb")>=0) {
					DB_TYPE = DataBaseConstant.DB_TYPE_MARIADB;
				}else {
					log.error("数据库类型:[" + dbType + "]不识别!");
					//throw new DBException("数据库类型:["+dbType+"]不识别!");
				}
			} catch (Exception e) {
				log.error(e.getMessage(), e);
			}finally {
				if(connection!=null && !connection.isClosed()){
					connection.close();
				}
			}
		}
		return DB_TYPE;
		
	}
	
	/**
	 * 根据connection对象获取数据库类型 调用此方法需要自己在外层手动关闭connection
	 * @param connection
	 * @return
	 * @throws SQLException
	 * @throws DBException
	 */
	public static String getDatabaseType(Connection connection) throws SQLException, DBException {
		if("".equals(DB_TYPE)) {
			DatabaseMetaData md = connection.getMetaData();
			String dbType = md.getDatabaseProductName().toLowerCase();
			if(dbType.indexOf("mysql")>=0) {
				DB_TYPE = DataBaseConstant.DB_TYPE_MYSQL;
			}else if(dbType.indexOf("oracle")>=0) {
				DB_TYPE = DataBaseConstant.DB_TYPE_ORACLE;
			}else if(dbType.indexOf("sqlserver")>=0 || dbType.indexOf("sql server")>=0) {
				DB_TYPE = DataBaseConstant.DB_TYPE_SQLSERVER;
			}else if(dbType.indexOf("postgresql")>=0) {
				DB_TYPE = DataBaseConstant.DB_TYPE_POSTGRESQL;
			}else if(dbType.indexOf("mariadb")>=0) {
				DB_TYPE = DataBaseConstant.DB_TYPE_MARIADB;
			}else {
				log.error("数据库类型:[" + dbType + "]不识别!");
				//throw new DBException("数据库类型:["+dbType+"]不识别!");
			}
		}
		return DB_TYPE;
	}
	
//	public static String getDataType(Session session) throws SQLException, DBException{
//		return getDatabaseType();
//	}
	
	public static String getTableName(String tableName,String dataBaseType) {
		switch (dataBaseType) {
			case DataBaseConstant.DB_TYPE_ORACLE:
			case DataBaseConstant.DB_TYPE_DB2:
				return tableName.toUpperCase();
			case DataBaseConstant.DB_TYPE_POSTGRESQL:
				return tableName.toLowerCase();
			default:
				return tableName;
		}
	}

	public static Boolean judgeTableIsExit(String tableName) {
		return judgeTableIsExit(tableName, null);
	}
	
	/**
	  * 判断数据库表是否存在
	 * @param tableName
	 * @return
	 */
	public static Boolean judgeTableIsExit(String tableName, DataBaseConfig dbc) {
		Connection conn = null;
		ResultSet rs = null;
		try {
			String[] types = { "TABLE" };
			if(dbc==null){
				conn = DbTableUtil.getConnection();
			}else{
				conn = getDataSourceConnect(dbc);
			}
			DatabaseMetaData dbMetaData = conn.getMetaData();
			//update-begin-author:taoyan date:20210831 for: 【数据库兼容性】online表单同步逻辑 LOWCOD-1766
			DbType dbTypeEnum = DbTableUtil.getDbType(dbc);
			String dbTypeString = DbTypeUtils.getDbTypeString(dbTypeEnum);
			String tableNamePattern = getTableName(tableName, dbTypeString);
			//update-end-author:taoyan date:20210831 for: 【数据库兼容性】online表单同步逻辑 LOWCOD-1766
			//update-begin-author:taoyan date:20200904 for:Online表单开发，普通同步报错 LOWCOD-830 TODO sqlserver测试
			String user = null;
			if(dbc!=null){
				user = dbc.getUsername();
			}else{
				DataBaseConfig dataBaseConfig = SpringContextUtils.getBean(DataBaseConfig.class);
				user = dataBaseConfig.getUsername();
			}
			if(DbTypeUtils.dbTypeIsOracle(dbTypeEnum) || DbType.DB2.equals(dbTypeEnum)){
				//	ORACLE 下该参数必须大写才能获取数据
				user = user!=null?user.toUpperCase():null;
			}
			//update-begin-author:scott date:20201201 for:SQLSERVER不能设置第二个参数，不然出问题 【SQLSERVER不存在LOWCOD-830的问题】
			if(DbTypeUtils.dbTypeIsSQLServer(dbTypeEnum)){
				rs = dbMetaData.getTables(conn.getCatalog(), null, tableNamePattern, types);
			} else if(DbTypeUtils.dbTypeIsPostgre(dbTypeEnum)){
				//参数1-数据库名 参数2-schema默认取public 参数3-表名 参数4-标识查询类型为：表类型
				rs = dbMetaData.getTables(conn.getCatalog(), "public", tableNamePattern, types);
			}else if(DbType.HSQL.equals(dbTypeEnum)){
				// HSQL 表名大写
				rs = dbMetaData.getTables(conn.getCatalog(), "PUBLIC", tableNamePattern.toUpperCase(), types);
			}else {
				//参数1-数据库名 参数2-用户名  参数3-表名 参数4-表类型
				rs = dbMetaData.getTables(conn.getCatalog(), user, tableNamePattern, types);
			}
			//update-end-author:scott date:20201201 for:SQLSERVER不能设置第二个参数，不然出问题 【SQLSERVER不存在LOWCOD-830的问题】
			//update-end-author:taoyan date:20200904 for:Online表单开发，普通同步报错 LOWCOD-830
			if (rs.next()) {
				//log.info("数据库表：【"+tableName+"】已存在");
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			throw new RuntimeException();
		}finally{//关闭连接
			try {
				if(rs!=null){rs.close();}
				if(conn!=null){conn.close();}
			} catch (SQLException e) {
				log.error(e.getMessage(), e);
			}
		}
	}
	
	
	/**
	 * 获取列的Map
	 * key 是 cloumn_name value 是 List<Map<String, Object>>
	 * @param queryForList
	 * @return 
	 */
	public static Map<String, Object> getColumnMap(
			List<Map<String, Object>> queryForList) {
		Map<String, Object> columnMap = new HashMap<String, Object>();
		for(int i =0 ;i<queryForList.size();i++){
			columnMap.put(queryForList.get(i).get("column_name").toString(), queryForList.get(i));
		}
		return columnMap;
	}
	

	/**
	  * 获取方言
	 * @return
	 * @throws SQLException
	 * @throws DBException
	 */
	//TODO 方言获取,此方法不能通用那就直接在yml中配置死
	public static String getDialect() throws SQLException, DBException {
		String databaseType = getDatabaseType();
		return getDialect(databaseType);
	}
	
	
	public static String getDialect(String databaseType) throws SQLException, DBException {
		//String dialect = "org.hibernate.dialect.MySQLDialect";
		String dialect = "org.hibernate.dialect.MySQL5InnoDBDialect";
		switch (databaseType) {
			case DataBaseConstant.DB_TYPE_SQLSERVER:
				dialect = "org.hibernate.dialect.SQLServerDialect";
				break;
			case DataBaseConstant.DB_TYPE_POSTGRESQL:
				dialect = "org.hibernate.dialect.PostgreSQLDialect";
				break;
			case DataBaseConstant.DB_TYPE_ORACLE:
				dialect = "org.hibernate.dialect.OracleDialect";
				break;
			case DataBaseConstant.DB_TYPE_DM:
				dialect = "org.hibernate.dialect.DmDialect";
				break;
			default:
				break;
		}
		return dialect;
	}
	
	
	/**
	 * 把配置中的字段翻译成数据库中的字段  如:A ---> _a
	 * @param fileName
	 * @return
	 */
	public static String translatorToDbField(String fileName){
		
		//去掉转换
		return fileName;
//		String name = "";
//		char[] chars = fileName.toCharArray();
//		for(int i =0 ;i<chars.length;i++){
//			name+= chars[i]>'A'&&chars[i]<'Z'?("_"+Character.toLowerCase(chars[i])):chars[i];
//		}
//		return name;
	}


	/**
	 * 根据 数据源信息获取connect
	 * @param db
	 * @return
	 * @throws SQLException
	 */
	public static Connection getDataSourceConnect(DataBaseConfig db) throws SQLException {
		DriverManagerDataSource ds = new DriverManagerDataSource ();
		ds.setDriverClassName(db.getDriverClassName());
		ds.setUrl(db.getUrl());
		ds.setUsername(db.getUsername());
		ds.setPassword(db.getPassword());
		return ds.getConnection();
	}

	/**
	 * 根据 数据源信息获取 DbType
	 * @param dbc
	 * @return
	 */
	public static DbType getDbType(DataBaseConfig dbc){
		if(dbc==null){
			return CommonUtils.getDatabaseTypeEnum();
		}
		return JdbcUtils.getDbType(dbc.getUrl());
	}

}
